var crcDB = null;

/*! Initialize the crcDB global variable. */
function initDB() {

    if(crcDB === null){
    
        try {        
        
            var shortName = 'crcInv1';
            var version = '1.0';
            var displayName = 'CRC Database';
            var maxSize = 1024 * 1024 * 5; // in bytes

            crcDB = openDatabase(shortName, version, displayName, maxSize);
        }
        catch (e) {
            // Error handling code goes here.
            if (INVALID_STATE_ERR !== undefined && e == INVALID_STATE_ERR) {
                // Version number mismatch.
                alert("Invalid database version.");
            } else {
                alert("Unknown error " + e + ".");
            }
            return;
        }
        
    }

}

var errDBCallback = function (transaction, result, callback) {
    alert("SQL Error: " + result);
    console.log("SQL Error" + result);
}

/*This function is used to execute query*/
function ExecuteQuery(query, db) {
    try {
        console.log("Executeing query: " + query);
        //console.log(db);
        //console.log(db.transaction);        
        db.transaction(
            function (transaction) {
                //console.log("In");
                transaction.executeSql(query, [], function (tx, e) { console.log("Sucess" + e) }, function (tx, e) { console.log("Fail: " + e) });
                //console.log("In Completed");
            }
            );
        //console.log("Query Executed");
    }
catch (e) {
    console.log(e);
    }
}

//this function should be deprecated.
function ExecuteQueryforLogging(query, db, callback) {
    
    if (typeof callback !== 'function') {
        callback = function(){};
    }
    
    _execQuery(query, [], function(trx, dbResult){
        callback(trx, dbResult);
    });    
    
}

/*This function is used to execute the query and get the response*/
function ExecuteReader(query, db, success, fail) {
    try {
        console.log("Executing Select Query: " + query);
        //console.log(db);
        //console.log(db.transaction);
        db.transaction(
            function (transaction) {
                transaction.executeSql(query, [], success, fail);
            });
    }
    catch (e) {
        console.log(e);
    }
}

//whether to log or not to log queries executed.
var isQueryLoggingOff = false;

//whether to perform parsing on the SQL Insert statement for query logging.
//the option to avoid SQL parsing is not available for native WebSQL implementation.
//thus, this config is not relevant to such browsers (i.e., it will always parse SQL INSERT statements).
var avoidSQLParsingWhenLogging = true;

function _log(action, callback, db){
                   
    db = db || crcDB;
    
    var date = new Date()
        ,battery = typeof AppStatus === 'undefined' || !AppStatus.GetBattery ? undefined : AppStatus.GetBattery()
        ,connection = typeof AppStatus === 'undefined' || !AppStatus.GetConnection ? ''  : AppStatus.GetConnection();
        
    var log = {
        Logdate : date.getFullYear() + '-' + ("0" + (date.getMonth() + 1)).slice(-2) + '-' + date.getDate()
       ,Logtime : ("0" + date.getHours()).slice(-2) + ':' + ("0" + date.getMinutes()).slice(-2) + ':' + date.getSeconds()
       ,Batlevel : battery ? battery.level : "none"
       ,Batcharging : battery ? battery.charging : false
       ,Sigstrength : connection || "none"
       ,Wifi : connection === ' WiFi connection' ? true : false
       ,lat : localStorage.getItem("Latitude")
       ,long : localStorage.getItem("Longitude")
       ,Useraction : action.replace(/['".]/gi, '')
    };
    
    var insert = "INSERT INTO applog(Logdate,Logtime ,Batlevel,Batcharging,Sigstrength,Wifi,lat,long,Useraction)Values('"
                       + log.Logdate + "', '" + log.Logtime + "', '" + log.Batlevel + "','" + log.Batcharging + "','" 
                       + log.Sigstrength + "','" + log.Wifi + "', '" + log.lat + "','" + log.long + "',"
                       + '"' + log.Useraction + '"' + ")";

    //if avoidSQLParsingWhenLogging is disabled, or web sql is natively supported
   if( !avoidSQLParsingWhenLogging || false == SQLEngine.webSQL.noNativeWebSQL()  ){    
        _execQuery(insert, [], callback);
        return;
    }
    
    _execQuery(insert, [], callback, db, {
        nativeOp : 'insert'
       ,nativeOpParams : ['applog', log]
    });
    
}

/*
This should be the core query execution method for single queries.
Refactored this so that logging handling and logic will be more centralized.
*/
function _execQuery(query, params, callback, dbConn, options){

    options    = options || {};   
    dbConn    = dbConn || crcDB;
    params    = params || [];
    
    if (typeof callback !== 'function') {
        callback = function(trx, result_or_error, info, isError){};
    }

    var nativeOp = options.nativeOp || ''
         ,nativeOpParams = options.nativeOpParams || [];
    
    var startTransTime, transTime, startQueryTime
    ,callbackHandler = function(trx, result_or_error){
       
       var info = {
            transTime : transTime
           ,queryTime : ((new Date() - startQueryTime) / 1000)
           ,query : query
       }
       ,isError = result_or_error.code !== undefined && result_or_error.message !== undefined;

       var actions =  "Executed Query " + (nativeOp ? "(not parsed)" : "") + ": " + query 
                            + ", Trans Time: " + info.transTime.toString() + " seconds."
                            + ", Query time: " + info.queryTime.toString() + " seconds.";

        actions = !isError
                       ? (actions + ", Row(s) affected/returned: " + (result_or_error.rowsAffected ? result_or_error.rowsAffected : result_or_error.rows.length))
                       : (actions + ", SQL Error: " + result_or_error.message);
                        
       info.message = actions;
       console.log(actions);
       callback(trx, result_or_error, info, isError);
       
    };
    
    startTransTime = new Date();
    
    try {    
            
        dbConn.transaction(function(trx){
        
            transTime = ((new Date() - startTransTime) / 1000);
            startQueryTime = new Date();
            if( nativeOp !== '' ){
                trx[nativeOp].apply(trx, nativeOpParams.concat([callbackHandler, callbackHandler]));
            }
            else{
                trx.executeSql(query, params, callbackHandler, callbackHandler);
            }
            
        });
        
    }
    catch(e){
    
        transTime = ((new Date() - startTransTime) / 1000);
        startQueryTime = new Date();
        callbackHandler({}, {code : e.code || '', message : e.message || ''});
        
    }
       
}

function _execMultiQuery(queries, callback){

    callback = callback || function(){};
    
    var i, len, fns = [], _fn = function(query){
        return function(cb){
            _execQuery(query, [], function(){
                cb();
            });
        };
    };
    
    for(i = 0, len = queries.length; i < len; i++){
    
        fns = fns.concat([_fn(queries[i])]);
        
    }
    
    async.series(fns, callback);
    
}

// this function is in logging into applog so that it will not loop..
function ExecuteReaderWithSingleCallbackForLogging(query, db, callback) {

    if (typeof callback !== 'function') {
        callback = function(){};
    }
    
    _execQuery(query, [], function(trx, dbResult){
        callback(trx, dbResult);
    });
    
}

/*This function is used to execute the query and get the response fail and success in single function*/
function ExecuteReaderWithSingleCallback(query, db, callback) {
    
    //not sure if to log this or to throw an exception.        
    if (typeof callback !== 'function') {
        callback = function(){};
    }

    if (IsNullOrEmpty(query)) {
        callback(null, null);
        return;
    } 
    
    _execQuery(query, [], function(trx, dbResult, info, isError){
    
        if( isQueryLoggingOff === true ){
            callback(trx, dbResult);
            return;
        }
        
        _log(info.message || '', function(){
            callback(trx, dbResult);
        }, db);
                
    });
    
}

function ExecuteReaderWithSingleCallbackMulti(queries, db, callback){

    callback = callback || function(){};
    
    var i, len, fns = [], _fn = function(query){
        return function(cb){
            ExecuteReaderWithSingleCallback(query, db, function(){
                cb();
            });
        };
    };
    
    for(i = 0, len = queries.length; i < len; i++){
    
        fns = fns.concat([_fn(queries[i])]);
        
    }
    
    async.series(fns, callback);
    
}
/*This function is used to execute query Synchronously*/
function ExecuteSyncQuery(query, db) {
    console.log("Executing Sync Query: " + query);    
    var resultPar;
    var resultErr;
    db.transaction(
        function (transaction) {
            transaction.executeSql(query,
            function (tt, result) {
                console.log("Success");
                //console.log("Success" + tt);
                //console.log("Success" + result);
                resultPar = result;
            },
            function (tt, result) {
                console.log("Fail");
                // console.log("Fail" + tt);
                // console.log("Fail" + result); 
                resultErr = result;
            }
            );
        });

    //console.log(resultPar);
    //console.log(resultErr);
    //console.log("Completed Sync Query");
}

//callback format example: function (bRet) { alert(bRet); }; bRet = true if all quries runs fine.
//Do not use this one for time sensitive situation. Somehow it will cause weird problem.
function ExecuteMultipleQuery(queryList, db, callback) {
    if (!queryList || queryList.length <= 0) {
        callback(true);
        return;
    }

    
    db.transaction(function (transaction) {
        var bRet = true;
        for (var m = 0; m < queryList.length; m++) {
            try {
                console.log("Executing Query: " + queryList[m]);
                if (m < (queryList.length - 1))
                    transaction.executeSql(queryList[m], [], function (tt, result) { console.log("Success"); }, function (tt, result) { console.log("Fail"); bRet = false; });
                else
                    transaction.executeSql(queryList[m], [],
                        function (tt, result) {
                            console.log("Success");
                            callback(bRet);
                        },
                        function (tt, result) {
                            console.log("Fail");
                            bRet = false;
                            callback(bRet);
                        });
            }
            catch (e) {
                console.log("Failed: query = " + queryList[m]);
                bRet = false;
            }
        }

    });
}

//Helper function
function IsNumber(n) {
    return !isNaN(parseFloat(n)) && isFinite(n);
}

//If only want to check undefied or null, simply call if(var).
function IsNullOrEmpty(val) {
    return (val === undefined || val == null || val.length <= 0) ? true : false;
}

function _qry(query, callback) {

    crcDB.transaction(function (trxn) {
        trxn.executeSql(query, [], callback, callback);
    });

}

function _genInsertSQL(r, tableName){

    var sql =  [];
    for(var i = 0, len = r.rows.length; i < len; i++){

        var row = r.rows.item(i), fields = [], values = [], val;
    
        for(var fName in row){
            fields = fields.concat([fName]);
            val = row[fName];
            values = values.concat([typeof val === 'string' ? ("'" + val + "'") : (val == null ? 'null' : val)]);         
        }
    
        sql  = sql.concat(["INSERT INTO " + tableName + "(" + fields.join(",") + ") VALUES (" + values.join(",") + ")"]);
    
    }

    return sql.join(";");

}